package cfgs.gestudent.finestra.assistencia;

import java.awt.Color;

import javax.swing.DefaultComboBoxModel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.ListSelectionModel;

import java.awt.GridBagLayout;

import javax.swing.JTextField;

import java.awt.GridBagConstraints;

import javax.swing.JComboBox;

import java.awt.Insets;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.ResourceBundle;

import javax.swing.JTable;
import javax.swing.event.DocumentEvent;
import javax.swing.event.DocumentListener;
import javax.swing.table.DefaultTableModel;

import cfgs.gestudent.dao.assistencia.MostrarAssistencies;
import cfgs.gestudent.dao.curs.MostrarCursos;
import cfgs.gestudent.dao.grup.ObtenirGrup;
import cfgs.gestudent.preferencies.ConfiguracionsIdiomes;
import cfgs.gestudent.preferencies.PreferenciesAnyAcademic;

import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;

import javax.swing.JLabel;

import org.freixas.jcalendar.JCalendarCombo;

public class FinestraMostrarAssistenciaGrup extends JPanel {
	/**
	 * 
	 */
	private static final long serialVersionUID = 7478936272021703380L;
	
	private JTextField txtCercar;
	private JTable table;
	private DefaultTableModel dtmEjemplo;
	private JScrollPane scpTable;
	
	private String whereGrup = "";
	private String whereDates = "";
	
	
	private JComboBox comboCurs;
	private JComboBox comboGrup;
	private JComboBox comboCercar;
	
	
	private ConfiguracionsIdiomes configs = new ConfiguracionsIdiomes();
	private ResourceBundle rb = configs.getIdioma();
	
	private String cursSelec = null;
	private ObtenirGrup og = new ObtenirGrup();
	private MostrarCursos mc = new MostrarCursos();
	private MostrarAssistencies ma;
	private int idCursTabla;
	private int idGrupTabla;
	private int idAssignTabla;
	
	private boolean clickCurs = false;
	private boolean clickGrup = false;
	private JLabel lblFiltrarPerDates;
	
	
	
	/**
	 * Create the panel.
	 */
	public FinestraMostrarAssistenciaGrup() {
		
		setBackground(Color.WHITE);
		GridBagLayout gridBagLayout = new GridBagLayout();
		gridBagLayout.columnWidths = new int[] {0, 40, 40, 0, 0, 0};
		gridBagLayout.rowHeights = new int[]{0, 0, 0, 0};
		gridBagLayout.columnWeights = new double[]{0.0, 1.0, 1.0, 1.0, 1.0, Double.MIN_VALUE};
		gridBagLayout.rowWeights = new double[]{0.0, 1.0, 0.0, Double.MIN_VALUE};
		setLayout(gridBagLayout);
		mc.llistesComboBox();
		
		
		
		
		comboCurs = new JComboBox();
		
		comboCurs.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent arg0) {
				
				int index = comboCurs.getSelectedIndex();
				idCursTabla = mc.getLlistaIdIndex(index);
				
				clickCurs = true;
				//Carregar dades comboGrup
				cursSelec = comboCurs.getSelectedItem().toString();
				og.llistesComboBoxId(idCursTabla);
				comboGrup.setModel(new DefaultComboBoxModel<>(og.getLlista().toArray()));
				
				comboGrup.setEnabled(true);//habilitar
				
				//linies carregar dades filtrades noves
				
				ma = new MostrarAssistencies();
				String where = "AND c.id="+idCursTabla;
				String sql = obtenirSQL(where);
				
				ma.setSql(sql);
				dtmEjemplo = new DefaultTableModel(ma.getFilas(), ma.getColumnas()){

					@Override
					public boolean isCellEditable(int arg0, int arg1) {
						return false;
					}

				};
				table.setModel(dtmEjemplo);
			}
		});
		comboCurs.setModel(new DefaultComboBoxModel(mc.getLlista().toArray()));
		GridBagConstraints gbc_comboCurs = new GridBagConstraints();
		gbc_comboCurs.insets = new Insets(0, 0, 5, 5);
		gbc_comboCurs.fill = GridBagConstraints.HORIZONTAL;
		gbc_comboCurs.gridx = 3;
		gbc_comboCurs.gridy = 0;
		add(comboCurs, gbc_comboCurs);
		
		comboGrup = new JComboBox();
		comboGrup.setEnabled(false);
		comboGrup.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent arg0) {
				clickGrup = true;
				int indexgrup = comboGrup.getSelectedIndex();
				idGrupTabla = og.getLlistaIdIndex(indexgrup);
				
				comboCercar.setEnabled(true);
				
				//carregar taula
				ma = new MostrarAssistencies();
				whereGrup = "AND c.id="+idCursTabla+" AND gc.Id="+idGrupTabla;
				ma.setSql(obtenirSQL("AND c.id="+idCursTabla+" AND gc.Id="+idGrupTabla));
				
				dtmEjemplo = new DefaultTableModel(ma.getFilas(), ma.getColumnas()){

					@Override
					public boolean isCellEditable(int arg0, int arg1) {
						return false;
					}

				};
				table.setModel(dtmEjemplo);
			}
		});
		GridBagConstraints gbc_comboGrup = new GridBagConstraints();
		gbc_comboGrup.insets = new Insets(0, 0, 5, 0);
		gbc_comboGrup.fill = GridBagConstraints.HORIZONTAL;
		gbc_comboGrup.gridx = 4;
		gbc_comboGrup.gridy = 0;
		add(comboGrup, gbc_comboGrup);
		
		comboCercar = new JComboBox();
		comboCercar.setEnabled(false);
		comboCercar.setModel(new DefaultComboBoxModel(new String[] {rb.getString("cercarPerCombAl"), rb.getString("nomCombAl"), rb.getString("cognomCombAl"), rb.getString("cursCombAl"), rb.getString("grupCombAl")}));
		comboCercar.addActionListener(new ActionListener() {
			
			@Override
			public void actionPerformed(ActionEvent arg0) {
				String selecCombo = comboCercar.getSelectedItem().toString();
				if(selecCombo != rb.getString("cercarPerCombAl")){
					txtCercar.enable();
				}else{
					txtCercar.disable();
				}
			}
		});
		
		lblFiltrarPerDates = new JLabel(rb.getString("lblFiltroDates"));
		GridBagConstraints gbc_lblFiltrarPerDates = new GridBagConstraints();
		gbc_lblFiltrarPerDates.insets = new Insets(0, 0, 0, 5);
		gbc_lblFiltrarPerDates.anchor = GridBagConstraints.EAST;
		gbc_lblFiltrarPerDates.gridx = 2;
		gbc_lblFiltrarPerDates.gridy = 2;
		add(lblFiltrarPerDates, gbc_lblFiltrarPerDates);
		
		GridBagConstraints gbc_comboCercar = new GridBagConstraints();
		gbc_comboCercar.insets = new Insets(0, 0, 0, 5);
		gbc_comboCercar.fill = GridBagConstraints.HORIZONTAL;
		gbc_comboCercar.gridx = 3;
		gbc_comboCercar.gridy = 2;
		add(comboCercar, gbc_comboCercar);
		
		txtCercar = new JTextField();
		txtCercar.getDocument().addDocumentListener(new DocumentListener() {
			
			@Override
			public void removeUpdate(DocumentEvent arg0) {
				String selecCombo = comboCercar.getSelectedItem().toString();
				if (selecCombo != rb.getString("cercarPerCombAl")) {
					
					String txtValue = txtCercar.getText();
					/**
					 * Nom
					 */
					if (selecCombo == rb.getString("nomCombAl")) {
						if(clickCurs == true){
							whereGrup = " AND c.id="
									+ idCursTabla
									+ " AND al.Nom LIKE'%"
									+ txtValue
									+ "%' ";
							String sql = obtenirSQL(whereGrup);
							
						}else if(clickGrup == true){
							whereGrup = " AND c.id="
									+ idCursTabla
									+ " AND gc.Id="
									+ idGrupTabla
									+ " AND al.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}else{
							whereGrup=" AND al.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}
						/**
						 * Cognom
						 */
					} else if (selecCombo == rb.getString("cognomCombAl")) {
						if(clickCurs == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND al.Cognoms LIKE'%"
									+ txtValue
									+ "%' ";
						}else if(clickGrup == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND gc.Id="
									+ idGrupTabla
									+ " AND al.Cognoms LIKE'%"
									+ txtValue
									+ "%' ";
						}else{
							whereGrup=" AND al.Cognoms LIKE'%"
									+ txtValue
									+ "%' ";
						}
						
						/**
						 * grup
						 */
					} else if (selecCombo == rb.getString("grupCombAl")) {
						if(clickCurs == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND gc.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}else if(clickGrup == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND gc.Id="
									+ idGrupTabla
									+ " AND gc.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}else{
							whereGrup= " AND gc.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}
						
						/**
						 * curs
						 */
					} else if (selecCombo == rb.getString("cursCombAl")) {
						if(clickCurs == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND c.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}else if(clickGrup == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND gc.Id="
									+ idGrupTabla
									+ " AND c.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}else{
							whereGrup=" AND c.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}
					}
					String sql = obtenirSQL(whereGrup);
					if (sql != "") {
						ma.setSql(sql);
						dtmEjemplo = new DefaultTableModel(ma.getFilas(), ma
								.getColumnas()) {

							@Override
							public boolean isCellEditable(int arg0, int arg1) {
								return false;
							}

						};
						table.setModel(dtmEjemplo);
					}
				}

			}

			@Override
			public void insertUpdate(DocumentEvent arg0) {
				String selecCombo = comboCercar.getSelectedItem().toString();
if (selecCombo != rb.getString("cercarPerCombAl")) {
					
					String txtValue = txtCercar.getText();
					/**
					 * Nom
					 */
					if (selecCombo == rb.getString("nomCombAl")) {
						if(clickCurs == true){
							whereGrup = " AND c.id="
									+ idCursTabla
									+ " AND al.Nom LIKE'%"
									+ txtValue
									+ "%' ";
							String sql = obtenirSQL(whereGrup);
							
						}else if(clickGrup == true){
							whereGrup = " AND c.id="
									+ idCursTabla
									+ " AND gc.Id="
									+ idGrupTabla
									+ " AND al.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}else{
							whereGrup=" AND al.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}
						/**
						 * Cognom
						 */
					} else if (selecCombo == rb.getString("cognomCombAl")) {
						if(clickCurs == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND al.Cognoms LIKE'%"
									+ txtValue
									+ "%' ";
						}else if(clickGrup == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND gc.Id="
									+ idGrupTabla
									+ " AND al.Cognoms LIKE'%"
									+ txtValue
									+ "%' ";
						}else{
							whereGrup=" AND al.Cognoms LIKE'%"
									+ txtValue
									+ "%' ";
						}
						
						/**
						 * grup
						 */
					} else if (selecCombo == rb.getString("grupCombAl")) {
						if(clickCurs == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND gc.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}else if(clickGrup == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND gc.Id="
									+ idGrupTabla
									+ " AND gc.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}else{
							whereGrup= " AND gc.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}
						
						/**
						 * curs
						 */
					} else if (selecCombo == rb.getString("cursCombAl")) {
						if(clickCurs == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND c.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}else if(clickGrup == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND gc.Id="
									+ idGrupTabla
									+ " AND c.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}else{
							whereGrup=" AND c.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}
					}
					String sql = obtenirSQL(whereGrup);
					if (sql != "") {
						ma.setSql(sql);
						dtmEjemplo = new DefaultTableModel(ma.getFilas(), ma
								.getColumnas()) {

							@Override
							public boolean isCellEditable(int arg0, int arg1) {
								return false;
							}

						};
						table.setModel(dtmEjemplo);
					}
				}

			}

			@Override
			public void changedUpdate(DocumentEvent arg0) {
				String selecCombo = comboCercar.getSelectedItem().toString();
if (selecCombo != rb.getString("cercarPerCombAl")) {
					
					String txtValue = txtCercar.getText();
					/**
					 * Nom
					 */
					if (selecCombo == rb.getString("nomCombAl")) {
						if(clickCurs == true){
							whereGrup = " AND c.id="
									+ idCursTabla
									+ " AND al.Nom LIKE'%"
									+ txtValue
									+ "%' ";
							String sql = obtenirSQL(whereGrup);
							
						}else if(clickGrup == true){
							whereGrup = " AND c.id="
									+ idCursTabla
									+ " AND gc.Id="
									+ idGrupTabla
									+ " AND al.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}else{
							whereGrup=" AND al.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}
						/**
						 * Cognom
						 */
					} else if (selecCombo == rb.getString("cognomCombAl")) {
						if(clickCurs == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND al.Cognoms LIKE'%"
									+ txtValue
									+ "%' ";
						}else if(clickGrup == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND gc.Id="
									+ idGrupTabla
									+ " AND al.Cognoms LIKE'%"
									+ txtValue
									+ "%' ";
						}else{
							whereGrup=" AND al.Cognoms LIKE'%"
									+ txtValue
									+ "%' ";
						}
						
						/**
						 * grup
						 */
					} else if (selecCombo == rb.getString("grupCombAl")) {
						if(clickCurs == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND gc.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}else if(clickGrup == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND gc.Id="
									+ idGrupTabla
									+ " AND gc.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}else{
							whereGrup= " AND gc.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}
						
						/**
						 * curs
						 */
					} else if (selecCombo == rb.getString("cursCombAl")) {
						if(clickCurs == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND c.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}else if(clickGrup == true){
							whereGrup=" AND c.id="
									+ idCursTabla
									+ " AND gc.Id="
									+ idGrupTabla
									+ " AND c.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}else{
							whereGrup=" AND c.Nom LIKE'%"
									+ txtValue
									+ "%' ";
						}
					}
					String sql = obtenirSQL(whereGrup);
					if (sql != "") {
						ma.setSql(sql);
						dtmEjemplo = new DefaultTableModel(ma.getFilas(), ma
								.getColumnas()) {

							@Override
							public boolean isCellEditable(int arg0, int arg1) {
								return false;
							}

						};
						table.setModel(dtmEjemplo);
					}
				}
			}
		});
		GridBagConstraints gbc_txtCercar = new GridBagConstraints();
		gbc_txtCercar.fill = GridBagConstraints.HORIZONTAL;
		gbc_txtCercar.gridx = 4;
		gbc_txtCercar.gridy = 2;
		add(txtCercar, gbc_txtCercar);
		txtCercar.setColumns(10);

		table = new JTable();
		ma = new MostrarAssistencies();
		ma.setSql(obtenirSQL(""));
		dtmEjemplo = new DefaultTableModel(ma.getFilas(), ma.getColumnas()){

			@Override
			public boolean isCellEditable(int arg0, int arg1) {
				return false;
			}
			
		};
		table.setModel(dtmEjemplo);
		scpTable = new JScrollPane();
		scpTable.setViewportView(table);
		
		table.setSelectionMode(ListSelectionModel.SINGLE_INTERVAL_SELECTION);

		GridBagConstraints gbc_table = new GridBagConstraints();
		gbc_table.insets = new Insets(0, 0, 5, 0);
		gbc_table.gridwidth = 5;
		gbc_table.fill = GridBagConstraints.BOTH;
		gbc_table.gridx = 0;
		gbc_table.gridy = 1;
		add(scpTable, gbc_table);
						
		
		
		
		
		
	}

	private String obtenirSQL(String where){
		String sql= "SELECT c.Nom, gc.Nom, al.Nom AS nom, al.Cognoms AS cognom, "
				+" (SELECT COUNT(asi.Assistit) FROM ASSISTENCIA asi WHERE asi.Id_Alumne=al.Id AND asi.Assistit=1) AS assistit, "
				+" (SELECT COUNT(asi.Retard) FROM ASSISTENCIA asi WHERE asi.Id_Alumne=al.Id AND asi.Retard=1) AS retard, "
				+" (SELECT COUNT(asi.Falta) FROM ASSISTENCIA asi WHERE asi.Id_Alumne=al.Id AND asi.Falta=1) AS falta, "
				+" (SELECT COUNT(asi.Falta_Justificada) FROM ASSISTENCIA asi WHERE asi.Id_Alumne=al.Id AND asi.Falta_Justificada=1) AS justificada "
				+ "FROM ASSISTENCIA asi, ALUMNE al, IMPARTIR_ASSIGNATURA ia, GRUP_CURS gc, CURS c, ANY_ACADEMIC aa WHERE al.Id=asi.Id_Alumne  "
				+ "AND asi.Id_Impartir_Assignatura=ia.Id AND ia.Id_Grup_Curs=gc.Id AND gc.Id_Curs=c.Id AND c.Id_Any_Academic=aa.Id "
				+ "AND aa.Id="+veureIdAnyAncademic()+" "+where+" "+whereDates+" GROUP BY al.Id ORDER BY al.Cognoms, c.Id, gc.Id";
		
		return sql;
	}
	
	private int veureIdAnyAncademic(){
		int idAny = 0;
		
		PreferenciesAnyAcademic paa = new PreferenciesAnyAcademic();
		idAny = paa.getAnyAcademic();
		
		return idAny;
	}

}
